import 'package:floor/floor.dart';
import 'package:share_fit/app/dao/dao_mixin.dart';
import 'package:share_fit/app/models/home/spo2_model.dart';

@dao
abstract class Spo2Dao with DaoMixin<Spo2Model> {
  @override
  @Query('SELECT * FROM spo2 WHERE _id = :id')
  Stream<Spo2Model?> findById(int id);

  @Query('SELECT * FROM spo2 where address = :address and calendar = :calendar order by calendar desc, time desc limit 1')
  Future<Spo2Model?> findLatestOne(String address, String calendar);

  @override
  @Query('SELECT * FROM spo2')
  Future<List<Spo2Model>> findAll();

  /// 获取当天内所有的血氧记录
  @Query("SELECT oxygen_value, calendar_time "
      "FROM spo2 WHERE address = :address and calendar = :calendar GROUP BY calendar_time order by calendar_time")
  Future<List<Spo2Model>> findAllBy(String address, String calendar);

  /// 获取当天内按小时分组的血氧记录
  @Query("SELECT CAST(AVG(oxygen_value) AS INT) as oxygen_value, STRFTIME('%H', calendar_time) as calendar "
      "FROM spo2 WHERE calendar = :calendar and address = :address GROUP BY strftime('%H', calendar_time) order by calendar")
  Future<List<Spo2Model>> groupByHour(String address, String calendar);

  /// 获取当天内按分钟分组的血氧记录
  @Query("SELECT CAST(AVG(oxygen_value) AS INT) as oxygen_value, strftime('%Y-%m-%d %H:%M', calendar_time) as calendar "
      "FROM spo2 WHERE calendar = :calendar and address = :address GROUP BY strftime('%Y-%m-%d %H:%M', calendar_time) order by calendar")
  Future<List<Spo2Model>> findTodayData(String address, String calendar);

  /// 根据日期条件获取时间范围内平均血氧记录
  @Query("SELECT "
      "CAST(AVG(oxygen_value) AS INT) as oxygen_value,  calendar "
      "FROM spo2 WHERE calendar >= :calendarStart and calendar <= :calendarEnd and address = :address GROUP BY calendar order by calendar")
  Future<List<Spo2Model>> findInRange(String address, String calendarStart, String calendarEnd);

  @override
  @Query('SELECT * FROM spo2')
  Stream<List<Spo2Model>> findAllAsStream();

  @override
  @Query('delete FROM spo2')
  Future<void> deleteAll();
  
  @Query('SELECT * FROM spo2 WHERE date_created >= :startTime and date_created < :endTime')
  Future<List<Spo2Model>> findBy(int startTime, int endTime);

  /// 查找指定月份中有血氧数据的日期
  @Query("select distinct calendar from spo2 "
      "where address = :address and STRFTIME('%Y', calendar_time) >= :minYear and STRFTIME('%Y', calendar_time) <= :maxYear")
  Future<List<String>> findHasRecordDays(String address, String minYear, String maxYear);

  @Query('SELECT oxygen_value FROM spo2 where address = :address and calendar >= :dateStart and calendar <= :dateEnd')
  Future<List<Spo2Model>> findDataDetail(String address, String dateStart, String dateEnd);

  @Query('delete FROM spo2 where address = :address')
  @override
  Future<void> deleteAllByAddress(String address);
}